IF OBJECT_ID('ssf.gbl_set_semaphore_connection_count') IS NOT NULL
	DROP PROCEDURE ssf.gbl_set_semaphore_connection_count
GO

CREATE PROCEDURE ssf.gbl_set_semaphore_connection_count
	@semaphore_name NVARCHAR(128)
	, @connection_count INT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@semaphore_name, N'') IS NULL
OR @connection_count IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END

IF @connection_count < 1
BEGIN
	RAISERROR('@connection_count must be at least 1.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[initialize connection records]]></code_comment>*/
DECLARE @merge_output TABLE (
	semaphore_name NVARCHAR(128)
	, connection_id INT
	, merge_action NVARCHAR(128)
)

BEGIN TRANSACTION

BEGIN TRY
	;
	WITH connections (semaphore_name, connection_id) AS (
		SELECT @semaphore_name
			, 1
		WHERE ssf.gbl_exists_variable(@semaphore_name) = 1
		UNION ALL
		SELECT semaphore_name
			, connection_id + 1
		FROM connections
		WHERE connection_id < @connection_count
	)
	MERGE
	INTO ssf.gbl_semaphore_connection WITH (ROWLOCK) AS g
	USING connections c
	ON g.semaphore_name = c.semaphore_name
	AND g.connection_id = c.connection_id
	WHEN MATCHED
	AND g.locked_by_spid IS NULL
	THEN UPDATE SET g.modified_date = SYSDATETIME()
	WHEN NOT MATCHED BY TARGET
	THEN INSERT (
		semaphore_name
		, connection_id
		, locked_by_spid
		, locked_by_user
		, modified_date
	)
	VALUES (
		c.semaphore_name
		, c.connection_id
		, NULL
		, NULL
		, SYSDATETIME()
	)
	WHEN NOT MATCHED BY SOURCE
	AND g.locked_by_spid IS NULL
	THEN DELETE
	OUTPUT ISNULL(INSERTED.semaphore_name, DELETED.semaphore_name)
		, ISNULL(INSERTED.connection_id, DELETED.connection_id)
		, $action
	INTO @merge_output (
		semaphore_name
		, connection_id
		, merge_action
	)
	OPTION (MAXRECURSION 0);

	SELECT @row_count = @@ROWCOUNT
		, @sql_error = @@ERROR

	IF @sql_error <> 0
		RAISERROR('Failed to enable connections for the semaphore <%s>.', 16, 1, @semaphore_name)

	-- verify correct number of connections exist
	IF NOT EXISTS (
		SELECT 1
		FROM @merge_output
		WHERE merge_action IN ('INSERT', 'UPDATE')
		HAVING COUNT(*) = @connection_count
	)
		RAISERROR('Failed to enable <%d> connections for the semaphore <%s>.', 16, 1, @connection_count, @semaphore_name)
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END CATCH

COMMIT TRANSACTION
-- ============

RETURN @@ERROR
GO
